﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Common;
using System.Data;

using CoyoEden.Models;

namespace CoyoEden.Providers
{
	public partial class DbPortalProvider:PortalProvider
	{
        public override Community SelectCommunity(int id)
		{
			var item = Community.Communities.Find(c=>c.Id==id);
			if(item!=null)
				item.MarkOld();
			return item;
		}

        public override void InsertCommunity(Community item)
		{
			List<Community> items = Community.Communities;
			items.Add(item);

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("INSERT INTO {0}Communities (Name, Description,Slug,Icon,ParentID) ",tablePrefix);
					sql.AppendFormat("VALUES ({0}name, {0}description,{0}slug,{0}icon, {0}parentid);",parmPrefix);
					sql.AppendFormat("SELECT Id FROM {0}Communities WHERE Name={1}name;",tablePrefix,parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "name";
					dpTitle.Value = item.Name;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpDesc = MyDbFactory.CreateParameter();
					dpDesc.ParameterName = parmPrefix + "description";
					dpDesc.Value = item.Description;
					cmd.Parameters.Add(dpDesc);

					DbParameter dpSlug = MyDbFactory.CreateParameter();
					dpSlug.ParameterName = parmPrefix + "slug";
					dpSlug.Value = item.Slug;
					cmd.Parameters.Add(dpSlug);

					DbParameter dpIcon = MyDbFactory.CreateParameter();
					dpIcon.ParameterName = parmPrefix + "icon";
					dpIcon.Value = item.Icon;
					cmd.Parameters.Add(dpIcon);

					DbParameter dpParent = MyDbFactory.CreateParameter();
					dpParent.ParameterName = parmPrefix + "parentid";
					dpParent.Value = item.Parent;
					cmd.Parameters.Add(dpParent);

					//cmd.ExecuteNonQuery();
					var cid = cmd.ExecuteScalar();
					item.Id = Convert.ToInt32(cid);
				}
			}
		}

        public override void UpdateCommunity(Community item)
		{
			List<Community> communities = Community.Communities;
			communities.Remove(item);
			communities.Add(item);

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("UPDATE {0}Communities ",tablePrefix);
					sql.AppendFormat("SET Name = {0}name, ",parmPrefix);
					sql.AppendFormat("Description = {0}description,Slug={0}slug,Icon={0}icon, ParentID ={0}parentid ",parmPrefix);
					sql.AppendFormat("WHERE Id = {0}id",parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "name";
					dpTitle.Value = item.Name;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpDesc = MyDbFactory.CreateParameter();
					dpDesc.ParameterName = parmPrefix + "description";
					dpDesc.Value = item.Description;
					cmd.Parameters.Add(dpDesc);

					DbParameter dpSlug = MyDbFactory.CreateParameter();
					dpSlug.ParameterName = parmPrefix + "slug";
					dpSlug.Value = item.Slug;
					cmd.Parameters.Add(dpSlug);

					DbParameter dpIcon = MyDbFactory.CreateParameter();
					dpIcon.ParameterName = parmPrefix + "icon";
					dpIcon.Value = item.Icon;
					cmd.Parameters.Add(dpIcon);

					DbParameter dpParent = MyDbFactory.CreateParameter();
					dpParent.ParameterName = parmPrefix + "parentid";
					dpParent.Value = item.Parent;
					cmd.Parameters.Add(dpParent);

					cmd.ExecuteNonQuery();
				}
			}
		}

        public override void DeleteCommunity(Community item)
		{
			List<Community> communities = Community.Communities;
			communities.Remove(item);

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("DELETE FROM {0}CommunityTopic ",tablePrefix);
					sql.AppendFormat("WHERE CommunityID = {0}cid;",parmPrefix);
					sql.AppendFormat("DELETE FROM {0}Communities ",tablePrefix);
					sql.AppendFormat("WHERE Id = {0}cid",parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "cid";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();
				}
			}
		}

        public override List<Community> FillCommunities()
		{
			List<Community> items = new List<Community>();

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;

				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("SELECT Id, Name, Description,Slug,Icon,ParentID ");
					sql.AppendFormat("FROM {0}Communities ",tablePrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;
					conn.Open();

					using (DbDataReader rdr = cmd.ExecuteReader())
					{
						if (rdr.HasRows)
						{
							while (rdr.Read())
							{
								Community item = new Community();
								item.Name = rdr.GetString(1);
								item.Description = (rdr.IsDBNull(2)) ? "" : rdr.GetString(2);
								item.Slug = rdr.GetString(3);
								item.Icon = (rdr.IsDBNull(4)) ? "" : rdr.GetString(4);
								item.Parent = (rdr.IsDBNull(5)) ? -1 : rdr.GetInt32(5);
								item.Id = rdr.GetInt32(0);
								items.Add(item);
								item.MarkOld();
							}
						}
					}
				}
			}

			return items;
		}
	}
}
